package ModelLayer;

import java.sql.*;
import java.util.ArrayList;

/**
 * Dette er klassen ProductDB.
 * 
 * @author Gruppe 4
 * @version 6/4 2014
 */
public class ProductDB {
	private static DbConnection conDB;
	private static Connection con;
	

	/**
	 * Dette er konstruktoren for klassen ProductDB
	 */
	public ProductDB() {

		conDB = DbConnection.getInstance();
		con = conDB.getDBcon();
	}
		
	
	
	public static void insertProduct(Product proObj)
	{
		String name = proObj.getName();
		String type = proObj.getType();
		int purchasePrice = proObj.getPurchasePrice();
		int salesPrice = proObj.getSalesPrice();
		int rentPrice = proObj.getRentPrice();
		String countryOfOrigin = proObj.getCountryOfOrigin();
		int minStock = proObj.getMinStock();
		int stock = proObj.getStock();
		int supplierID = proObj.getSupplierID();
		
		try{
			conDB.startTransaction();
			PreparedStatement st = con.prepareStatement("Insert Into Product(name,purchasePrice,salesPrice,rentPrice,countryOfOrigin,minStock,type,stock,supplierID) Values(?,?,?,?,?,?,?,?,?)");
			st.setString(1, name);
			st.setInt(2, purchasePrice);
			st.setInt(3, salesPrice);
			st.setInt(4, rentPrice);
			st.setString(5, countryOfOrigin);
			st.setInt(6, minStock);
			st.setString(7, type);
			st.setInt(8, stock);
			st.setInt(9, supplierID);
			st.setQueryTimeout(5);
			conDB.commitTransaction();
			st.execute();
			st.close();
	         }
	         catch(Exception e)
	         {
	        	 conDB.rollbackTransaction();
	        	 System.out.println("Couldn't commit product");
	         }
	}
	
	public ArrayList<Product> retrieveProducts()
	{
		ArrayList<Product> products = new ArrayList<Product>();
		try{
			String query = "SELECT * FROM Product";
			conDB.startTransaction();
			Statement smnt = con.createStatement();
			smnt.setQueryTimeout(5);
			ResultSet results = smnt.executeQuery(query);
			
			while( results.next() ){
		       String name = results.getString("name");
	           int productID = results.getInt("ProductID");
	           String type = results.getString("type");
	           int purchasePrice = results.getInt("PurchasePrice");
	           int salesPrice = results.getInt("PurchasePRice");
	           int rentPrice = results.getInt("RentPrice");
	           String countryOfOrigin = results.getString("CountryOfOrigin");
	           int minStock = results.getInt("minStock");
	           int stock = results.getInt("stock");
	           int supplierID = results.getInt("SupplierID");
	           
	           Product product = new Product(name, type, purchasePrice, salesPrice,
	        		   rentPrice, countryOfOrigin, minStock, stock, supplierID);
	           product.setProductID(productID);
	           products.add(product);
	           
		}
	       smnt.close();
		}
		catch(Exception e)
		{
			System.out.println("Couldn't retrieve products");
		}
		
		return products;
		
		
	}
}